package cn.yunhe;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class BaseDB {
    static Connection conn = null;
    static PreparedStatement psmt = null;
    static ResultSet rs = null;
    static String driverload;
    static String url;
    static String username;
    static String pass;

    /**
     * 获得链接
     *
     * @return
     */
    public static Connection getConn() {
        try {
            getD();
            Class.forName(driverload);
            conn = DriverManager.getConnection(url, username, pass);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return conn;
    }

    /**
     * 获取配置文件
     *
     * @throws Exception
     */
    private static void getD() throws Exception {
        InputStream in = BaseDB.class.getClassLoader().getResourceAsStream("db.properties");
        Properties pro = new Properties();
        pro.load(in);
        driverload = pro.getProperty("driverload");
        url = pro.getProperty("url");
        username = pro.getProperty("username");
        pass = pro.getProperty("pass");
    }

    /**
     * 预编译增删改
     *
     * @param sql
     * @param arr
     */
    public static int updateSql2(String sql, Object[] arr) {
        Connection conn = getConn();
        PreparedStatement psmt = null;
        int count = 0;
        try {
            psmt = conn.prepareStatement(sql);
            setZhan(psmt, arr);
            count = psmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, psmt, null);
        }
        return count;
    }


    /**
     * 通用查询
     *
     * @param sql
     * @param arr
     * @return
     */
    public static List selectSql(String sql, Object[] arr) {
        Connection conn = getConn();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        List<List> list = new ArrayList();
        try {
            psmt = conn.prepareStatement(sql);
            setZhan(psmt, arr);
            rs = psmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            int col = rsd.getColumnCount();
            while (rs.next()) {
                List list1 = new ArrayList();
                for (int i = 1; i <= col; i++) {
                    list1.add(rs.getString(i));
                }
                list.add(list1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, psmt, rs);
        }
        return list;
    }




    /**
     * 设置占位符参数
     *
     * @param psmt
     * @param arr
     */
    public static void setZhan(PreparedStatement psmt, Object[] arr) {
        if (psmt == null || arr == null)
            return;
        try {
            for (int i = 1; i <= arr.length; i++) {
                psmt.setObject(i, arr[i - 1]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 关闭所有
     *
     * @param conn
     * @param st
     * @param rs
     */
    public static void closeAll(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
